﻿using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using Production.CommonLib.Models;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace Production.ApplicationLib.Queries
{
    public class ProcessQueries : IProcessQueries
    {
        private readonly string? _defaultConnStr;

        public ProcessQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<PaginatedResult<ProcessInfoResponseDto>> QueryProcessesByPage(ProcessSearchRequestDto model)
        {
            IEnumerable<ProcessInfoResponseDto>? data;
            int totalCount;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessCategoryId,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_PROCESS t where 1 = 1 ");

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<ProcessInfoResponseDto>(query.SqlPage);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount);
            }

            return await PaginatedResult<ProcessInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }

        public async Task<Result<IList<ProcessInfoResponseDto>>?> QueryProcesses(ProcessSearchRequestDto model)
        {
            IEnumerable<ProcessInfoResponseDto>? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<ProcessInfoResponseDto>(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessCategoryId,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_PROCESS t where 1 = 1 ");
            }

            return await Result<IList<ProcessInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<Result<ProcessInfoResponseDto>?> GetProcess(int id)
        {
            ProcessInfoResponseDto? data;

            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryFirstOrDefaultAsync<ProcessInfoResponseDto>(@"
select t.Id,
       t.Code,
       t.Name,
       t.ProcessCategoryId,
       t.Description,
       t.DataState,
       t.CreatedBy,
       t.CreatedOn,
       t.LastModifiedBy,
       t.LastModifiedOn,
       t.DeletedBy,
       t.DeletedOn
from MES_PROCESS t where 1 = 1 and id = @id ", new { id });
            }

            return await Result<ProcessInfoResponseDto>.SuccessAsync(data);
        }
    }
}